<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>foreach: common_schema documentation</title>
	<meta name="description" content="foreach: common_schema" />
	<meta name="keywords" content="foreach: common_schema" />
	<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>

<body>
	<div id="main">
		<div id="header">
			<h1>common_schema</h1> <strong>2.2</strong> documentation
			<div class="subtitle">DBA's framework for MySQL</div>
		</div>
		<div id="contentwrapper">
			<div id="content">
				<h2><a href="foreach.html">foreach</a></h2>	
<h3>NAME</h3>
foreach(): Invoke a script on each element of given collection. <strong>$()</strong> is a synonym of this routine.

<h3>TYPE</h3>
Procedure

<h3>DESCRIPTION</h3>
<p>
This procedure accepts collections of varying types, including result sets, and invokes a
<a href="query_script.html">QueryScript</a> code per element.
</p>
<p>
The script can be as simple as a query, a set of queries, or a complex code.
</p>
<p>The <i>foreach()</i> routine differs from the <a href="query_script_foreach.html">foreach</a> flow control structure in QueryScript, though
they both use similar syntax and share some use cases. Read <a href="query_script_foreach.html#notes">here</a> on the differences between the two.</p>
<p>
<i>foreach()</i> passes on information about iterated values onto the script in two ways:
<ul>
	<li>Using <i>place holders</i> (e.g. <strong>${1}, ${2}</strong> etc.)
		<br/>In this approach the script's text is manipulated such that placeholder occurrences are
		replaced with iterated values. This is a simple text search & replace approach, is very flexible,
		and allows for a lot of META tweaks.
		<br/>See following tables and examples for more on placeholders.
	</li>
	<li>Using <a href="query_script_input.html">input</a> variables:
		<br/>Variables are passed on to the script as input variables. These are dynamic variables, on which
		the genral rules for user defined variables apply.
	</li>
</ul> 
</p>
<p>
<i>foreach()</i> acts on server side only, and does not require shell access nor the <i>mysql</i>
command line client, although it may be spawned from within the <i>mysql</i> client.
</p>

<p>
<i>foreach()</i> accepts several types of collections. They are automatically recognized by their
pattern. The following collections are recognized (also see EXAMPLES section below):
<ul>
	<li><strong>A SELECT query</strong>: any <strong>SELECT</strong> statement makes for a collection,
		which is the result set of the query.
		<br/>The query must specify result columns. That is, a <strong>SELECT *</strong> query is not valid.
		<br/>Otherwise any SELECT query is valid, with any result set. However, only first <strong>9</strong> 
		columns in the result set can be used as place holders for the callback queries.
		<br/>Each row in the result set is an element. 
		<br/>The queries are allowed to act upon the table(s) being iterated, i.e. one can execute a 
		DELETE on rows being iterated.
		<br/>The place holders <strong>${1}</strong> - <strong>${9}</strong> relate to columns <strong>#1</strong> - <strong>#9</strong>.
	</li>
	<li><strong>Numbers range</strong>: a range of integers, both inclusive, e.g. <strong>'1970:2038'</strong>.
		<br/>Negative values are allowed. The first (left) value should be smaller or equal to the second (right) value,
		or else no iteration is performed.
		<br/>The place holder <strong>${1}</strong> indicates the iterated value.
	</li>
	<li><strong>Two dimensional numbers range</strong>: a double range of integers, e.g. <strong>'-10:10,1970:2038'</strong>.
		<br/>Each one of the ranges answers to the same rules as for a single range.
		<br/>There will be <strong>m * n</strong> iterations on ranges of size <strong>m</strong> and <strong>n</strong>. For example,
		in the sample range above there will be <strong>11 * 69</strong> iterations (or elements).
		<br/>The place holders <strong>${1}, ${2}</strong> indicate the iterated values.
	</li>
	<li><strong>A constants set</strong>: a predefined set of constant values, e.g. <strong>'{red, green, blue}'</strong>.
		<br/>Constants are separated by either spaces or commas (or both). 
		<br/>Constants can be quoted so as to allow spaces or commas within constant value. Quotes themselves are discarded.
		<br/>Empty constants are discarded.
		<br/>The place holder <strong>${1}</strong> indicates the current constant value.
	</li>
	<li><strong>'schema'</strong>: this is the collection of available schemata (e.g. as with <strong>SHOW DATABASES</strong>).
		<br/>The place holder <strong>${1}</strong> indicates the current schema. <strong>${schema}</strong> is a synonym for <strong>${1}</strong>.		
	</li>
	<li><strong>'schema like <i>expr</i>'</strong>: databases whose names match the given <strong>LIKE</strong> expression.
		<br/>The place holder <strong>${1}</strong> indicates the current schema. <strong>${schema}</strong> is a synonym for <strong>${1}</strong>.		
	</li>
	<li><strong>'schema ~ <i>/regexp/</i>'</strong>: databases whose names match the given regular expression.
		<br/>The place holder <strong>${1}</strong> indicates the current schema. <strong>${schema}</strong> is a synonym for <strong>${1}</strong>.		
	</li>
	<li><strong>'table in <i>schema_names</i>'</strong>: collection of all tables in given schema. Only tables are included: views are not listed.
		<br/>This syntax is <strong>INFORMATION_SCHEMA</strong> friendly, in that it only scans and opens <strong>.frm</strong> files for
		given schema.
		<br/>The place holder <strong>${1}</strong> indicates the current table. <strong>${table}</strong> is a synonym for <strong>${1}</strong>.		
		<br/>The place holder <strong>${2}</strong> indicates the schema. <strong>${schema}</strong> is a synonym for <strong>${2}</strong>.		
		<br/>The place holder <strong>${3}</strong> indicates the storage engine. <strong>${engine}</strong> is a synonym for <strong>${3}</strong>.		
		<br/>The place holder <strong>${4}</strong> indicates the CREATE_OPTIONS <strong>${create_options}</strong> is a synonym for <strong>${4}</strong>.		
	</li>
	<li><strong>'table like <i>expr</i>'</strong>: all tables whose names match the given <strong>LIKE</strong> expression. These can be tables
		from different databases/schemata.
		<br/>This syntax is <strong>INFORMATION_SCHEMA</strong> friendly, in that it only scans and opens <strong>.frm</strong> files for
		a single schema at a time. This reduces locks and table cache entries, while potentially taking longer to complete.
		<br/>The place holder <strong>${1}</strong> indicates the current table. <strong>${table}</strong> is a synonym for <strong>${1}</strong>.		
		<br/>The place holder <strong>${2}</strong> indicates the schema for current table. <strong>${schema}</strong> is a synonym for <strong>${2}</strong>.		
		<br/>The place holder <strong>${3}</strong> indicates the storage engine. <strong>${engine}</strong> is a synonym for <strong>${3}</strong>.		
		<br/>The place holder <strong>${4}</strong> indicates the CREATE_OPTIONS <strong>${create_options}</strong> is a synonym for <strong>${4}</strong>.		
	</li>
	<li><strong>'table ~ <i>/regexp/</i>'</strong>: all tables whose names match the given regular expression. These can be tables
		from different databases/schemata.
		<br/>This syntax is <strong>INFORMATION_SCHEMA</strong> friendly, in that it only scans and opens <strong>.frm</strong> files for
		a single schema at a time. This reduces locks and table cache entries, while potentially taking longer to complete.
		<br/>The place holder <strong>${1}</strong> indicates the current table. <strong>${table}</strong> is a synonym for <strong>${1}</strong>.		
		<br/>The place holder <strong>${2}</strong> indicates the schema for current table. <strong>${schema}</strong> is a synonym for <strong>${2}</strong>.		
		<br/>The place holder <strong>${3}</strong> indicates the storage engine. <strong>${engine}</strong> is a synonym for <strong>${3}</strong>.		
		<br/>The place holder <strong>${4}</strong> indicates the CREATE_OPTIONS <strong>${create_options}</strong> is a synonym for <strong>${4}</strong>.		
	</li>
</ul>
Any other type of input raises an error.
</p>

<p>
Following is a brief sample of valid collection input:
<table>
	<tr><th>Collection type</th><th>Example of valid input</th></tr>
	<tr><td>SELECT query</td><td>'SELECT id, name FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time &gt; 20'</td></tr>
	<tr><td>Numbers range</td><td>'1970:2038'</td></tr>
	<tr><td>Two dimensional numbers range</td><td>'0:23,0:59'</td></tr>
	<tr><td>Constants set</td><td>'{USA, "GREAT BRITAIN", FRA, IT, JP}'</td></tr>
	<tr><td>'schema'</td><td>'schema'</td></tr>
	<tr><td>'schema like <i>expr</i>'</td><td>'schema like customer_%'</td></tr>
	<tr><td>'schema ~ <i>/regexp/</i>'</td><td>'schema ~ /^customer_[0-9]+$/</td></tr>
	<tr><td>'table in <i>schema_name</i>'</td><td>'table in sakila'</td></tr>
	<tr><td>'table like <i>expr</i>'</td><td>'table like wp_%'</td></tr>
	<tr><td>'table ~ <i>/regexp/</i>'</td><td>'table ~ /^state_[A-Z]{2}$/'</td></tr>
</table>
</p>

<p>
The following table summarizes the types of collections and the valid place holders:
<table>
	<tr><th>Collection type</th><th>Valid place holders</th></tr>
	<tr><td>SELECT query</td><td>${1}, ${2}, ..., ${9}, ${NR}</td></tr>
	<tr><td>Numbers range</td><td>${1}, ${NR}</td></tr>
	<tr><td>Two dimensional numbers range</td><td>${1}, ${2}, ${NR}</td></tr>
	<tr><td>Constants set</td><td>${1}, ${NR}</td></tr>
	<tr><td>'schema'</td><td>${1} or ${schema}, ${NR}</td></tr>
	<tr><td>'schema like <i>expr</i>'</td><td>${1} or ${schema}, ${NR}</td></tr>
	<tr><td>'schema ~ <i>/regexp/</i>'</td><td>${1} or ${schema}, ${NR}</td></tr>
	<tr><td>'table in <i>schema_name</i>'</td><td>${1} or ${table), ${2} or ${schema}, ${3} or ${engine}, ${4} or ${create_options}, ${NR}</td></tr>
	<tr><td>'table like <i>expr</i>'</td><td>${1} or ${table), ${2} or ${schema}, ${3} or ${engine}, ${4} or ${create_options}, ${NR}</td></tr>
	<tr><td>'table ~ <i>/regexp/</i>'</td><td>${1} or ${table), ${2} or ${schema}, ${3} or ${engine}, ${4} or ${create_options}, ${NR}</td></tr>
</table>
<strong>${NR}</strong> is accepted in all collections, and returns the iteration index, <strong>1</strong> based. That is,
the first element in a collection has <strong>1</strong> for <strong>${NR}</strong>, the seconds has <strong>2</strong>, etc.
It is similar in concept to <strong>${NR}</strong> in <i>awk</i>.
</p>

<p>
	Invoker of this procedure must have the privileges required for execution of given queries.
</p>

<h3>SYNOPSIS</h3>
<p>
<blockquote><pre>foreach(collection TEXT CHARSET utf8, execute_queries TEXT CHARSET utf8)</pre></blockquote>
</p>
<p>
  Input:
  <ul>
    <li><strong>collection</strong>: the collection on which to iterate; must be in a recognized format as discussed above. 
    </li>
    <li><strong>execute_queries</strong>: one or more queries to execute per loop iteration.
    	<br/>Queries are separated by semicolons (<strong>;</strong>). See <a href="exec.html">exec()</a> for 
    	details.
    </li>
  </ul>
  Since the routines relies on <a href="exec.html">exec()</a>, it accepts the following
  input config:
  <ul>
    <li><strong>@common_schema_dryrun</strong>: when <strong>1</strong>, queries are not executed, but rather printed.</li>
    <li><strong>@common_schema_verbose</strong>: when <strong>1</strong>, queries are verbosed.</li>
  </ul>
  Output:
  <ul>
    <li>Whatever output the queries may produce.</li>
  </ul>
</p>

<h3>EXAMPLES</h3>
<ul>
	<li>SELECT query
		<p>
		Kill queries for user <strong>'analytics'</strong>. 
		<br/>We take advantage of the fact we do not use <a href="http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_ansi_quotes">ANSI_QUOTES</a>,
		and so we are able to use nicer quoting scheme, as with JavaScript or Python.
		</p>
		<blockquote><pre>mysql&gt; call foreach(
	"SELECT id FROM INFORMATION_SCHEMA.PROCESSLIST WHERE user = 'analytics'", 
	'KILL QUERY ${1}');
</pre></blockquote>
		<p>
		Select multiple columns; execute multiple queries based on those columns:
		</p>
		<blockquote><pre>mysql&gt; call foreach(
	"SELECT Code, Name FROM world.Country WHERE Continent='Europe'", 
	"DELETE FROM world.CountryLanguage WHERE CountryCode = '${1}'; 
	DELETE FROM world.City WHERE CountryCode = '${1}'; 
	DELETE FROM Country WHERE Code = '${1}'; 
	INSERT INTO logs (msg) VALUES ('deleted country: name=${2}');");
</pre></blockquote>		
	</li>
	
	<li>Numbers range:
		<p>
		Delete records from July-August for years <strong>2001</strong> - <strong>2009</strong>:
		</p>
		<blockquote><pre>mysql&gt; call foreach(
	'2001:2009', 
	"DELETE FROM sakila.rental WHERE rental_date >= '${1}-07-01' AND rental_date < '${1}-09-01'");
</pre></blockquote>		

		<p>
		Generate tables; use <i>$()</i> synonym of <i>foreach()</i>:
		</p>
		<blockquote><pre>mysql&gt; call $('1:50', "CREATE TABLE test.t_${1} (id INT)");
		
mysql&gt; SHOW TABLES FROM test;
+----------------+ 
| Tables_in_test | 
+----------------+ 
| from_file      | 
| t              | 
| t_1            | 
| t_10           | 
| t_11           | 
| t_12           |
| t_13           |
| t_14           |
...
+----------------+ 		
</pre></blockquote>		
	</li>
	
	<li>Two dimensional numbers range:
		<p>
		Fill in data for all tables generated on last step:
		</p>
		<blockquote><pre>mysql&gt; call foreach('1:50,1970:2038', "INSERT INTO test.t_${1} VALUES (${2})");
</pre></blockquote>		
	</li>
	
	<li>Constants set:
		<p>
		Generate databases:
		</p>
		<blockquote><pre>mysql&gt; call foreach('{US, GB, Japan, FRA}', 'CREATE DATABASE db_${1}');

mysql&gt; show databases LIKE 'db_%';
+-----------------+
| Database (db_%) |
+-----------------+
| db_FRA          |
| db_GB           |
| db_Japan        |
| db_US           |
+-----------------+
</pre></blockquote>		
	</li>

	<li>'schema':
		<p>
		List full tables on all schemata:
		</p>
		<blockquote><pre>mysql&gt; call foreach('schema', "SHOW FULL TABLES FROM ${schema}");
+---------------------------------------+-------------+
| Tables_in_information_schema          | Table_type  |
+---------------------------------------+-------------+
| CHARACTER_SETS                        | SYSTEM VIEW |
| COLLATIONS                            | SYSTEM VIEW |
| COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW |
| COLUMNS                               | SYSTEM VIEW |
| COLUMN_PRIVILEGES                     | SYSTEM VIEW |
...
+---------------------------------------+-------------+

...
		
+-----------------+------------+
| Tables_in_world | Table_type |
+-----------------+------------+
| City            | BASE TABLE |
| Country         | BASE TABLE |
| CountryLanguage | BASE TABLE |
| Region          | BASE TABLE |
+-----------------+------------+
</pre></blockquote>		
	</li>
	
	<li>'schema like <i>expr</i>':
		<p>
		Create a new table in all hosted WordPress schemata:
		</p>
		<blockquote><pre>mysql&gt; call foreach(
	'schema like wp%', 
	'CREATE TABLE ${schema}.wp_likes(id int, data VARCHAR(128))');
		</pre></blockquote>
	</li>
	
	<li>'schema ~ <i>/regexp/</i>':
		<p>
		Likewise, be more accurate on schema name:
		</p>
		<blockquote><pre>mysql&gt; call foreach(
	'schema ~ /^wp_[\d]+$/', 
	'CREATE TABLE ${schema}.wp_likes(id int, data VARCHAR(128))');
		</pre></blockquote>
	</li>
	
	<li>'table in <i>schema_name</i>':
		<p>
		Convert all tables in <strong>world</strong> to InnoDB:
		</p>
		<blockquote><pre>mysql&gt; call $('table in world',	'ALTER TABLE ${schema}.${table} ENGINE=InnoDB');
		</pre></blockquote>
	</li>

	<li>'table like <i>expr</i>':
		<p>
		Add a column to all <strong>wp_posts</strong> tables in hosted WordPress databases:
		</p>
		<blockquote><pre>mysql&gt; call foreach(
	'table like wp_posts', 
	'ALTER TABLE ${schema}.${table} ADD COLUMN post_geo_location VARCHAR(128);');
		</pre></blockquote>
	</li>

	<li>'table ~ <i>/regexp/</i>':
		<p>
		Add a column to tables whose name matches the given regular expression, in any database:
		</p>
		<blockquote><pre>mysql&gt; call foreach(
	'table ~ /^customer_data_[\d]+$/', 
	'ALTER TABLE ${schema}.${table} ADD COLUMN customer_geo_location VARCHAR(128);');
		</pre></blockquote>
	</li>
</ul>

<h3>ENVIRONMENT</h3>
MySQL 5.1 or newer

<h3>SEE ALSO</h3>
<a href="exec.html">exec()</a>,
<a href="exec_single.html">exec_single()</a>,
<a href="repeat_exec.html">repeat_exec()</a>

<h3>AUTHOR</h3>
Shlomi Noach, Roland Bouman

				<br/>
			</div>
			<div id="sidebarwrapper">
				<div id="search">
					Search online documentation
					<form id="search_form" name="search_form" method="GET" 
						action="http://www.google.com/search" 
						onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
						<input type="text" name="search_term" value=""/>
						<input type="hidden" name="q" value=""/>
						<input type="submit" value="go"/>						
					</form>
				</div>
				<div id="menu">
					<ul>
						<li><a title="Introduction" href="introduction.html">Introduction</a></li>
						<li><a title="Documentation" href="documentation.html">Documentation</a></li>
						<li><a title="Download" href="download.html">Download</a></li>
						<li><a title="Install" href="install.html">Install</a></li>
						<li><a title="Risks" href="risks.html">Risks</a></li>
					</ul>						
					<h3>QUERY SCRIPT</h3>
					<ul>
						<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
						<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
						<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
						<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
						<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
						<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
					</ul>						
					<h3>DEBUG</h3>
					<ul>
						<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
						<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
						<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
					</ul>						
					<h3>ROUTINES</h3>
					<ul>
						<li><a title="Execution &amp; flow control" href="execution_routines.html">Execution & flow control</a></li>
						<li><a title="General" href="general_routines.html">General</a></li>
						<li><a title="Process" href="process_routines.html">Process</a></li>
						<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
						<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
						<li><a title="Security" href="security_routines.html">Security</a></li>
						<li><a title="Text" href="text_routines.html">Text</a></li>
						<li><a title="Time &amp; date" href="temporal_routines.html">Time & date</a></li>
						<li><a title="Charting" href="charting_routines.html">Charting</a></li>
					</ul>
					<h3>VIEWS</h3>
					<ul>
						<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
						<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
						<li><a title="Process" href="process_views.html">Process</a></li>
						<li><a title="Security" href="security_views.html">Security</a></li>
						<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
						<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
						<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
						<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
					</ul>						
					<h3>DATA</h3>
					<ul>
						<li><a title="tables" href="tables.html">Tables</a></li>
						<li><a title="variables" href="variables.html">Variables</a></li>
					</ul>						
					<h3>META</h3>
					<ul>
						<li><a title="Help" href="help.html">help</a></li>
						<li><a title="Metadata" href="metadata.html">metadata</a></li>
						<li><a title="status" href="status.html">status</a></li>
					</ul>						
				</div>
			</div>	
			<div class="clear">&nbsp;</div>
			
			<div id="footnote" align="center">
				<a href="">common_schema</a> documentation
			</div>
		</div>
	</div>
</body>
</html>
